from sqlalchemy import create_engine, Column, Integer, String, DATETIME, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import random

# TODO: db_uri
# dialect+driver://username:password@host:port/database?charset=utf8
DB_URI = 'mysql+pymysql://root:root123@127.0.0.1:3300/first_sqlalchemy?charset=utf8'

engine = create_engine(DB_URI)

Base = declarative_base(bind=engine)

session = sessionmaker(bind=engine)()


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(11), nullable=False)
    price = Column(Integer, default=0, nullable=False)
    create_time = Column(DATETIME, default=datetime.now)

    def __repr__(self):
        return '<Article(title={title}, price={price})>'.format(title=self.title, price=self.price)


# TODO: 删除数据表
# Base.metadata.drop_all()
# TODO: 创建数据表
# Base.metadata.create_all()

# TODO: 创建测试数据
# for x in range(6):
#     article = Article(title='title%s' % x, price=random.randint(50, 100))
#     session.add(article)

# TODO: 提交数据
# session.commit()

# TODO: 1.模型对象。指定查找这个模型中所有的对象。
result1 = session.query(Article).all()
print('模型对象。指定查找这个模型中所有的对象。.....')
print(result1)

# TODO: 2.模型中的属性。可以指定只查找某个模型的其中几个属性。
result2 = session.query(Article.title, Article.price, Article.create_time).all()
print('模型中的属性。可以指定只查找某个模型的其中几个属性。.....')
print(result2)

# TODO: 3.聚合函数
# TODO: 3.1 func.count统计行的数量
counts = session.query(func.count(Article.id)).first()
print('func.count统计行的数量.....')
print(counts)

# TODO: 3.2 func.avg求平均值
avgprice = session.query(func.avg(Article.price)).first()
print('func.avg求平均值.....')
print(avgprice)

# TODO: 3.3 func.max求最大值
maxprice = session.query(func.max(Article.price)).first()
print('func.max求最大值.....')
print(maxprice)

# TODO: 3.4 func.min求最小值
minprice = session.query(func.min(Article.price)).first()
print('func.min求最小值.....')
print(minprice)

# TODO: 3.5 func.sum求和
sumprice = session.query(func.sum(Article.price)).first()
print('func.sum求和....')
print(sumprice)